Databases

class: center, middle, inverse, title-slide

# Databases
### Kirill & Nicolas
### cynkra GmbH
### March 15, 2022

---

# Introduction

Organization of half-day R courses:

- Intro courses:
  * Tidyverse intro I
  * Base R intro/Tidyverse intro II
  * Data visualization I
  * Data visualization II
- Advanced courses:
  * Advanced tidyverse
  * R package creation
  * Working with database systems
  * Parallelization & efficient R programming
  * Databases (this course)

---

# Course material

Our course material currently is available from GitHub at

https://github.com/cynkra/bag-courses

Today we will be looking at the folder `5_databases`

---

# Download course material

![how to download](data:image/png;base64,#cynkra-repo-dl.png)

---

# General remarks

- We hope for these courses to be interactive: go ahead and ask if something is unclear!
- You can also write into the chat, which I will try to monitor when Kirill is presenting.
- We were asked to provide recordings of the courses for those of you who cannot join, so recording is activated.
- Per course unit, we offer 4 hours of follow up time; approach us with questions (nicolas@cynkra.com)!

---

# Goals for today

Playing the whole game!

- Extract
- Transform
- Load
- **Consume**

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- **Read whole tables**
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/11.webp")
background-size: contain
background-position: 100% 100%

# Read whole tables

.pull-left[
- {DBI} and {duckdb} package
- Connect
- Discover
- Read
- Query

Script: `databases_11.R`

```r
library(tidyverse)
library(DBI)
```
]

---

background-image: url("data:image/png;base64,#images/11-frame.webp")
background-size: contain
background-position: 100% 100%

# Read whole tables

.pull-left[
- {DBI} package
- Connect
- Discover
- Read
- Query

Script: `databases_11.R`

```r
library(tidyverse)
library(DBI)
```
]

---

# Connect to the database

First step when accessing the database.

```r
con_duckdb <- dbConnect(duckdb::duckdb())
con_duckdb
```

```
<duckdb_connection 0faa0 driver=<duckdb_driver 22170 dbdir=':memory:' read_only=FALSE>>
```

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Prepare database

Normally done in a preparatory step.

```r
dm::copy_dm_to(
  con_duckdb,
  dm::dm_pixarfilms(),
  set_key_constraints = FALSE,
  temporary = FALSE
)
```

---

# Discover tables

Where is my data?

```r
dbListTables(con_duckdb)
```

```
[1] "academy"         "box_office"      "genres"         
[4] "pixar_films"     "pixar_people"    "public_response"
```

```r
dbListFields(con_duckdb, "pixar_films")
```

```
[1] "number"       "film"         "release_date" "run_time"    
[5] "film_rating" 
```

Caveat: schemas, catalogs, ...

```sql
SELECT * FROM INFORMATION_SCHEMA.TABLES
```

---

# Read tables

Read entire tables into your local session, if you can afford it.

```r
df_pixar_films <- dbReadTable(con_duckdb, "pixar_films")
df_pixar_films
```

```
   number                film release_date run_time film_rating
1       1           Toy Story   1995-11-22       81           G
2       2        A Bug's Life   1998-11-25       95           G
3       3         Toy Story 2   1999-11-24       92           G
4       4      Monsters, Inc.   2001-11-02       92           G
5       5        Finding Nemo   2003-05-30      100           G
6       6     The Incredibles   2004-11-05      115          PG
7       7                Cars   2006-06-09      117           G
8       8         Ratatouille   2007-06-29      111           G
9       9              WALL-E   2008-06-27       98           G
10     10                  Up   2009-05-29       96          PG
11     11         Toy Story 3   2010-06-18      103           G
12     12              Cars 2   2011-06-24      106           G
13     13               Brave   2012-06-22       93          PG
14     14 Monsters University   2013-06-21      104           G
15     15          Inside Out   2015-06-19       95          PG
16     16   The Good Dinosaur   2015-11-25       93          PG
17     17        Finding Dory   2016-06-17       97          PG
18     18              Cars 3   2017-06-16      102           G
19     19                Coco   2017-11-22      105          PG
20     20       Incredibles 2   2018-06-15      118          PG
 [ reached 'max' / getOption("max.print") -- omitted 7 rows ]
```

---

# Read tables

Use `as_tibble()` to convert to a tibble for better display
and more robust operation.

```r
df_pixar_films <- dbReadTable(con_duckdb, "pixar_films")
as_tibble(df_pixar_films)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 5
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story 1995-11-22 81 G 
 2 2 A Bug&#039;s Life 1998-11-25 95 G 
 3 3 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. 2001-11-02 92 G 
 5 5 Finding Nemo 2003-05-30 100 G 
 6 6 The Incredibles 2004-11-05 115 PG 
 7 7 Cars 2006-06-09 117 G 
 8 8 Ratatouille 2007-06-29 111 G 
 9 9 WALL-E 2008-06-27 98 G 
10 10 Up 2009-05-29 96 PG 
# … with 17 more rows
</CODE></PRE>

---

# Execute queries

Write SQL code to define what data you want to see.

```r
dbGetQuery(con_duckdb, "SELECT * FROM pixar_films")
```

---

# Execute queries

Write complex SQL code to define what data you want to see.

```r
sql <- "
SELECT * FROM pixar_films
WHERE release_date >= '2020-01-01'
"
```

```r
dbGetQuery(con_duckdb, sql)
```

```
 number film release_date run_time film_rating
1 22 Onward 2020-03-06 102 PG
2 23 Soul 2020-12-25 100 PG
3 24 Luca 2021-06-18 151 N/A
4 25 Turning Red 2022-03-11 NA N/A
5 26 Lightyear 2022-06-17 NA N/A
6 27 <NA> 2023-06-16 155 Not Rated
```

---

# Execute queries

R 4.0 or later: use new-style string literals for mixing quotes.

```r
sql <- r"(
SELECT * FROM "pixar_films"
WHERE "release_date" >= '2020-01-01'
)"
```

```r
dbGetQuery(con_duckdb, sql)
```

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Further pointers

.pull-left[
## Quoting

```r
dbQuoteIdentifier(con_duckdb, "academy")
```

```
<SQL> "academy"
```

```r
dbQuoteLiteral(con_duckdb, "Toy Story")
```

```
<SQL> 'Toy Story'
```

```r
dbQuoteLiteral(con_duckdb, as.Date("2020-01-01"))
```

```
<SQL> '2020-01-01'::date
```

```r
glue::glue_sql(...)
```
]

.pull-right[
## Parameterized queries

```r
sql <- "
SELECT count(*) FROM pixar_films
WHERE release_date >= ?
"
dbGetQuery(con_duckdb, sql,
 params = list(as.Date("2020-01-01"))
)
```

```
  count_star()
1            6
```
]

---

background-image: url("data:image/png;base64,#images/11-frame.webp")
background-size: contain
background-position: 100% 100%

# Read whole tables: Exercises

.pull-left[
1. List all columns from the `box_office` table.
2. Read the `academy` table.
3. Read all records from the `academy` table that correspond to awards won
    - Hint: Use the query `"SELECT * FROM academy WHERE status = 'Won'"`
4. Use quoting and/or query parameters to stabilize the previous query.
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- **Let the database do the heavy lifting 1/2**
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/12.webp")
background-size: 40%
background-position: 100% 100%

# Downsizing on the database

.pull-left[
- {dbplyr} package (part of the tidyverse)
- Lazy tables
- `collect()`
- `select()`
- `filter()`
- `count()`

Script: `databases_12_1.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/12-frame.webp")
background-size: 40%
background-position: 100% 100%

# Downsizing on the database

.pull-left[
- {dbplyr} package (part of the tidyverse)
- Lazy tables
- `collect()`
- `select()`
- `filter()`
- `group_by()`, `summarize()`, `ungroup()`, `count()`

Script: `databases_12_1.R`

```r
library(tidyverse)
```
]

---

# Lazy tables

A pointer to a SQL table. The data is still on the database!

```r
pixar_films <- tbl(con_duckdb, "pixar_films")
pixar_films
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;pixar_films&gt; [?? x 5]
# Database: duckdb_connection
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story 1995-11-22 81 G 
 2 2 A Bug&#039;s Life 1998-11-25 95 G 
 3 3 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. 2001-11-02 92 G 
 5 5 Finding Nemo 2003-05-30 100 G 
 6 6 The Incredibles 2004-11-05 115 PG 
 7 7 Cars 2006-06-09 117 G 
 8 8 Ratatouille 2007-06-29 111 G 
 9 9 WALL-E 2008-06-27 98 G 
10 10 Up 2009-05-29 96 PG 
# … with more rows
</CODE></PRE>

---

# Read the whole table

```r
df_pixar_films <-
 pixar_films %>%
 collect()
df_pixar_films
```

---

# Select columns

With `select()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  select(1:3)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 3]
# Database: duckdb_connection
 number film release_date
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; 
 1 1 Toy Story 1995-11-22 
 2 2 A Bug&#039;s Life 1998-11-25 
 3 3 Toy Story 2 1999-11-24 
 4 4 Monsters, Inc. 2001-11-02 
 5 5 Finding Nemo 2003-05-30 
 6 6 The Incredibles 2004-11-05 
 7 7 Cars 2006-06-09 
 8 8 Ratatouille 2007-06-29 
 9 9 WALL-E 2008-06-27 
10 10 Up 2009-05-29 
# … with more rows
</CODE></PRE>
]

.pull-right[
## Under the hood

```r
pixar_films %>%
  select(1:3) %>%
  show_query()
```

```
<SQL>
SELECT "number", "film", "release_date"
FROM "pixar_films"
```
]

---

# Select columns and read

.pull-left[

```r
df_pixar_films_3 <-
 pixar_films %>%
 select(1:3) %>%
 collect()
df_pixar_films_3
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 3
 number film release_date
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; 
 1 1 Toy Story 1995-11-22 
 2 2 A Bug&#039;s Life 1998-11-25 
 3 3 Toy Story 2 1999-11-24 
 4 4 Monsters, Inc. 2001-11-02 
 5 5 Finding Nemo 2003-05-30 
 6 6 The Incredibles 2004-11-05 
 7 7 Cars 2006-06-09 
 8 8 Ratatouille 2007-06-29 
 9 9 WALL-E 2008-06-27 
10 10 Up 2009-05-29 
# … with 17 more rows
</CODE></PRE>
]

.pull-right[
## Data on the database not affected

```r
pixar_films %>%
  collect()
```

---

# Select rows

With `filter()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  filter(release_date >= "2020-01-01")
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 5]
# Database: duckdb_connection
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 22 Onward 2020-03-06 102 PG 
2 23 Soul 2020-12-25 100 PG 
3 24 Luca 2021-06-18 151 N/A 
4 25 Turning Red 2022-03-11 NA N/A 
5 26 Lightyear 2022-06-17 NA N/A 
6 27 NA 2023-06-16 155 Not Rated 
</CODE></PRE>
]

.pull-right[
## Under the hood

```r
pixar_films %>%
  filter(release_date >= "2020-01-01") %>%
  show_query()
```

```
<SQL>
SELECT *
FROM "pixar_films"
WHERE ("release_date" >= '2020-01-01')
```
]

---

# Select rows and read

.pull-left[

```r
df_pixar_films_202x <-
 pixar_films %>%
 filter(release_date >= "2020-01-01") %>%
 collect()
df_pixar_films_202x
```

<PRE class="fansi fansi-output"><CODE># A tibble: 6 × 5
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 22 Onward 2020-03-06 102 PG 
2 23 Soul 2020-12-25 100 PG 
3 24 Luca 2021-06-18 151 N/A 
4 25 Turning Red 2022-03-11 NA N/A 
5 26 Lightyear 2022-06-17 NA N/A 
6 27 NA 2023-06-16 155 Not Rated 
</CODE></PRE>
]

.pull-right[
## Data on the database not affected

```r
pixar_films %>%
  collect()
```

---

# Aggregate

With `group_by()` + `summarize()` + `ungroup()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  group_by(film_rating) %>%
  summarize(n = n()) %>%
  ungroup()
```

.pull-right[
## Under the hood

```r
pixar_films %>%
  group_by(film_rating) %>%
  summarize(n = n()) %>%
  ungroup() %>%
  show_query()
```

```
<SQL>
SELECT "film_rating", COUNT(*) AS "n"
FROM "pixar_films"
GROUP BY "film_rating"
```
]

---

# Aggregate

With `count()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  #
  #
  count(film_rating)
```

.pull-right[
## Under the hood

```r
pixar_films %>%
  #
  #
  count(film_rating) %>%
  show_query()
```

```
<SQL>
SELECT "film_rating", COUNT(*) AS "n"
FROM "pixar_films"
GROUP BY "film_rating"
```
]

---

# Aggregate and read

.pull-left[

```r
df_pixar_films_by_rating <-
 pixar_films %>%
 count(film_rating) %>%
 collect()
df_pixar_films_by_rating
```

<PRE class="fansi fansi-output"><CODE># A tibble: 4 × 2
 film_rating n
 &lt;chr&gt; &lt;dbl&gt;
1 G 13
2 PG 10
3 N/A 3
4 Not Rated 1
</CODE></PRE>
]

.pull-right[
## Data on the database not affected

```r
pixar_films %>%
  collect()
```

---

background-image: url("data:image/png;base64,#images/12-frame.webp")
background-size: 40%
background-position: 100% 100%

# Downsizing on the database: Exercises 1

*  Find several ways to select the 3 first columns
*  What happens if you include the name of a variable multiple times in a `select()` call?
*  Select all columns that contain underscores (use `contains()`)
*  Use `all_of()` to select 2 columns of your choice

---

background-image: url("data:image/png;base64,#images/12-frame.webp")
background-size: 40%
background-position: 100% 100%

# Downsizing on the database: Exercises 2

.pull-left[
Find all films that
1. Are rated "PG"
2. Had a run time below 95
3. Had a rating of "N/A" or "Not Rated"
4. Were released after and including year 2020
5. Have a missing name (`film` column) or `run_time`
6. Are a first sequel (the name ends with "2")
    - Hint: Bring the data into the R session before filtering
]

---

background-image: url("data:image/png;base64,#images/12-frame.webp")
background-size: 40%
background-position: 100% 100%

# Downsizing on the database: Exercises 3

1. How many films are stored in the table?
2. How many films released after 2005 are stored in the table?
3. What is the total run time of all films?
    - Hint: Use `summarize(sum(...))`, watch out for the warning
4. What is the total run time of all films, per rating?
    - Hint: Use `group_by()`

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- **Let the database do the heavy lifting 2/2**
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/12_2.webp")
background-size: 40%
background-position: 100% 100%

# Computing on the database

.pull-left[
- {dbplyr} package (part of the tidyverse)
- `mutate()`
- `arrange()`
- Grouped `summarize()` / `mutate()`

Script: `databases_12_2.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/12_2-frame.webp")
background-size: 40%
background-position: 100% 100%

# Computing on the database

.pull-left[
- {dbplyr} package (part of the tidyverse)
- `mutate()`
- `arrange()`
- Grouped `summarize()` / `mutate()`

Script: `databases_12_2.R`

```r
library(tidyverse)
```
]

---

# Transform

With `mutate()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  select(film, release_date) %>% 
  mutate(release_year = year(release_date))
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 3]
# Database: duckdb_connection
 film release_date release_year
 &lt;chr&gt; &lt;date&gt; &lt;dbl&gt;
 1 Toy Story 1995-11-22 1995
 2 A Bug&#039;s Life 1998-11-25 1998
 3 Toy Story 2 1999-11-24 1999
 4 Monsters, Inc. 2001-11-02 2001
 5 Finding Nemo 2003-05-30 2003
 6 The Incredibles 2004-11-05 2004
 7 Cars 2006-06-09 2006
 8 Ratatouille 2007-06-29 2007
 9 WALL-E 2008-06-27 2008
10 Up 2009-05-29 2009
# … with more rows
</CODE></PRE>
]

.pull-right[
## Under the hood

```r
pixar_films %>%
  select(film, release_date) %>% 
  mutate(release_year = year(release_date)) %>%
  show_query()
```

```
<SQL>
SELECT "film", "release_date", EXTRACT(year FROM "release_date") AS "release_year"
FROM "pixar_films"
```
]

---

# Transform and read

.pull-left[

```r
df_pixar_films_with_release_year <-
 pixar_films %>%
 select(film, release_date) %>% 
 mutate(release_year = year(release_date)) %>%
 collect()
df_pixar_films_with_release_year
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 3
 film release_date release_year
 &lt;chr&gt; &lt;date&gt; &lt;dbl&gt;
 1 Toy Story 1995-11-22 1995
 2 A Bug&#039;s Life 1998-11-25 1998
 3 Toy Story 2 1999-11-24 1999
 4 Monsters, Inc. 2001-11-02 2001
 5 Finding Nemo 2003-05-30 2003
 6 The Incredibles 2004-11-05 2004
 7 Cars 2006-06-09 2006
 8 Ratatouille 2007-06-29 2007
 9 WALL-E 2008-06-27 2008
10 Up 2009-05-29 2009
# … with 17 more rows
</CODE></PRE>
]

.pull-right[
## Data on the database not affected

```r
pixar_films %>%
  collect()
```

---

# Aggregate (complex)

With `group_by()` + `summarize()` + `ungroup()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  group_by(film_rating) %>%
  summarize(mean_run_time = mean(run_time)) %>%
  ungroup()
```

```
Warning: Missing values are always removed in SQL.
Use `mean(x, na.rm = TRUE)` to silence this warning
This warning is displayed only once per session.
```

.pull-right[
## Under the hood

```r
pixar_films %>%
  group_by(film_rating) %>%
  summarize(mean_run_time = mean(run_time)) %>%
  ungroup() %>% 
  show_query()
```

```
<SQL>
SELECT "film_rating", AVG("run_time") AS "mean_run_time"
FROM "pixar_films"
GROUP BY "film_rating"
```
]

---

# Transform and aggregate

With `count()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  count(release_year = year(release_date))
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 2]
# Database: duckdb_connection
 release_year n
 &lt;dbl&gt; &lt;dbl&gt;
 1 1995 1
 2 1998 1
 3 1999 1
 4 2001 1
 5 2003 1
 6 2004 1
 7 2006 1
 8 2007 1
 9 2008 1
10 2009 1
# … with more rows
</CODE></PRE>
]

.pull-right[
## Under the hood

```r
pixar_films %>%
  mutate(release_year = year(release_date)) %>%
  count(release_year) %>%
  show_query()
```

```
<SQL>
SELECT "release_year", COUNT(*) AS "n"
FROM (SELECT "number", "film", "release_date", "run_time", "film_rating", EXTRACT(year FROM "release_date") AS "release_year"
FROM "pixar_films") "q01"
GROUP BY "release_year"
```
]

---

# In-place aggregate (window functions)

With `add_count()`, like with data frames.

.pull-left[

```r
pixar_films %>%
  add_count(release_year = year(release_date)) %>%
  filter(n > 1) %>%
  select(film, release_year, n) %>% 
  arrange(release_date)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 3]
# Database: duckdb_connection
# Groups: release_year
# Ordered by: release_date
 film release_year n
 &lt;chr&gt; &lt;dbl&gt; &lt;dbl&gt;
1 Inside Out 2015 2
2 The Good Dinosaur 2015 2
3 Cars 3 2017 2
4 Coco 2017 2
5 Onward 2020 2
6 Soul 2020 2
7 Turning Red 2022 2
8 Lightyear 2022 2
</CODE></PRE>
]

.pull-right[
## Under the hood

```r
pixar_films %>%
  add_count(release_year = year(release_date)) %>%
  filter(n > 1) %>%
  arrange(release_date) %>% 
  select(film, release_year, n) %>% 
  show_query()
```

```
<SQL>
SELECT "film", "release_year", "n"
FROM (SELECT "number", "film", "release_date", "run_time", "film_rating", "release_year", COUNT(*) OVER (PARTITION BY "release_year") AS "n"
FROM (SELECT "number", "film", "release_date", "run_time", "film_rating", EXTRACT(year FROM "release_date") AS "release_year"
FROM "pixar_films") "q01") "q02"
WHERE ("n" > 1.0)
ORDER BY "release_date"
```
]

---

background-image: url("data:image/png;base64,#images/12_2-frame.webp")
background-size: 40%
background-position: 100% 100%

# Computing on the database: Exercises

.pull-left[
1. Add new columns `release_year` and `release_month`.
2. Use the new columns to compute the number of months since January 1970
   for each film
3. Compute the overall median run time, and the median run time per film rating
4. For each film except the last, compute how many days have passed until the next film.
    - Hint: Use `lag(..., order_by = ...)`
]

.pull-right[
5. Find the maximum number of days between releases of two G and two PG films.
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- **Basic ETL for one table**
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/13.webp")
background-size: 40%
background-position: 100% 100%

# Extract, Transform, Load

.pull-left[
- Obtain raw data
- Prepare database table
- Write it to a new database
- Consume

Script: `databases_13.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/13-frame.webp")
background-size: 40%
background-position: 100% 100%

# Extract, Transform, Load

.pull-left[
- Obtain raw data
- Prepare database table
- Write it to a new database
- Consume

Script: `databases_13.R`

```r
library(tidyverse)
```
]

---

# Extract: Raw data

From arbitrary source: CSV, Excel, web API, other database, ...

```r
pixar_films_raw <- pixarfilms::pixar_films
pixar_films_raw
```

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Transform: Derived data

- Fix type of `number` column
- Extract `franchise` and `sequel` columns

```r
pixar_films_clean
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 7
 number film franchise sequel release_date run_time film_rating
 &lt;int&gt; &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story Toy Story 1 1995-11-22 81 G 
 2 2 A Bug&#039;s Life A Bug&#039;s Life NA 1998-11-25 95 G 
 3 3 Toy Story 2 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. Monsters, Inc. NA 2001-11-02 92 G 
 5 5 Finding Nemo Finding Nemo NA 2003-05-30 100 G 
 6 6 The Incredibles The Incredibles NA 2004-11-05 115 PG 
 7 7 Cars Cars 1 2006-06-09 117 G 
 8 8 Ratatouille Ratatouille NA 2007-06-29 111 G 
 9 9 WALL-E WALL-E NA 2008-06-27 98 G 
10 10 Up Up NA 2009-05-29 96 PG 
# … with 17 more rows
</CODE></PRE>

---

# Create target database

Depending on workflow, often an existing database is used.

```r
db_path <- fs::path_abs("pixar.duckdb")
db_path
```

```
/Users/kirill/git/cynkra/bag/bag-courses/5_databases/pixar.duckdb
```

```r
fs::file_delete(db_path)
con <- dbConnect(duckdb::duckdb(dbdir = db_path))
con
```

```
<duckdb_connection b1490 driver=<duckdb_driver b5870 dbdir='/Users/kirill/git/cynkra/bag/bag-courses/5_databases/pixar.duckdb' read_only=FALSE>>
```

---

# Load: Write table to the database

```r
dbWriteTable(con, "pixar_films", pixar_films_clean)
dbExecute(con, "CREATE UNIQUE INDEX pixarfilms_pk ON pixar_films (film)")
```

```
[1] 0
```

```r
nrow(dbReadTable(con, "pixar_films"))
```

```
[1] 27
```

```r
dbDisconnect(con)
```

---

# Consume: share the file, open it

.pull-left[

```r
fs::dir_info() %>%
  arrange(desc(birth_time)) %>%
  head(2)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 2 × 18
 path type size permissi… modification_time user group
 &lt;fs::path&gt; &lt;fct&gt; &lt;fs::&gt; &lt;fs::per&gt; &lt;dttm&gt; &lt;chr&gt; &lt;chr&gt;
1 …uckdb.wal file 1.84K rw-r--r-- 2022-03-14 21:16:33 kiri… staff
2 …ar.duckdb file 12K rw-r--r-- 2022-03-14 21:16:33 kiri… staff
# … with 11 more variables: device_id &lt;dbl&gt;, hard_links &lt;dbl&gt;,
# special_device_id &lt;dbl&gt;, inode &lt;dbl&gt;, block_size &lt;dbl&gt;,
# blocks &lt;dbl&gt;, flags &lt;int&gt;, generation &lt;dbl&gt;,
# access_time &lt;dttm&gt;, change_time &lt;dttm&gt;, birth_time &lt;dttm&gt;
</CODE></PRE>
]

.pull-right[

```r
con <- dbConnect(duckdb::duckdb(dbdir = db_path, read_only = TRUE))
my_pixar_films <- tbl(con, "pixar_films")
my_pixar_films
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;pixar_films&gt; [?? x 7]
# Database: duckdb_connection
 number film franchise sequel release_d… run_t… film_…
 &lt;int&gt; &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story Toy Story 1 1995-11-22 81 G 
 2 2 A Bug&#039;s Life A Bug&#039;s … NA 1998-11-25 95 G 
 3 3 Toy Story 2 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. Monsters… NA 2001-11-02 92 G 
 5 5 Finding Nemo Finding … NA 2003-05-30 100 G 
 6 6 The Incredibles The Incr… NA 2004-11-05 115 PG 
 7 7 Cars Cars 1 2006-06-09 117 G 
 8 8 Ratatouille Ratatoui… NA 2007-06-29 111 G 
 9 9 WALL-E WALL-E NA 2008-06-27 98 G 
10 10 Up Up NA 2009-05-29 96 PG 
# … with more rows
</CODE></PRE>
]

---

background-image: url("data:image/png;base64,#images/13-frame.webp")
background-size: 40%
background-position: 100% 100%

# Exercises

1. Adapt the ETL workflow to convert the `run_time` column to a duration.
   - Hint: Use `mutate()` with `hms::hms(minutes = ...)` .
2. Re-run the workflow.

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- **Subtle issues to watch out for**
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/14.webp")
background-size: 40%
background-position: 100% 100%

# Caveats

.pull-left[
- Lazy tables vs. data frames/tibbles
- Order
- Logical/Boolean data type
- Imperfect translation
- Materialization

Script: `databases_14.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/14-frame.webp")
background-size: 40%
background-position: 100% 100%

# Caveats

.pull-left[
- Differences between database backends
- Lazy tables vs. data frames/tibbles
- Order
- Logical/Boolean data type
- Imperfect translation
- Materialization

Script: `databases_14.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Prepare SQLite database

Very similar to DuckDB, subtle differences.

```r
con_sqlite <- DBI::dbConnect(RSQLite::SQLite(), extended_types = TRUE)
dm::copy_dm_to(
 con_sqlite,
 dm::dm_pixarfilms(),
 set_key_constraints = FALSE,
 temporary = FALSE
)
```

---

# SQLite table

```r
pixar_films_sqlite <- tbl(con_sqlite, "pixar_films")
pixar_films_sqlite
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;pixar_films&gt; [?? x 5]
# Database: sqlite 3.37.2 []
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story 1995-11-22 81 G 
 2 2 A Bug&#039;s Life 1998-11-25 95 G 
 3 3 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. 2001-11-02 92 G 
 5 5 Finding Nemo 2003-05-30 100 G 
 6 6 The Incredibles 2004-11-05 115 PG 
 7 7 Cars 2006-06-09 117 G 
 8 8 Ratatouille 2007-06-29 111 G 
 9 9 WALL-E 2008-06-27 98 G 
10 10 Up 2009-05-29 96 PG 
# … with more rows
</CODE></PRE>

---

# Lazy tables are not data frames

The bracket operators `[` and `[[` don't work.

.pull-left[
## Bad

```r
pixar_films[c("film", "film_rating")]
```

```
$<NA>
NULL

$<NA>
NULL
```
]

.pull-right[

## Good

```r
pixar_films %>%
  select(film, film_rating)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 2]
# Database: duckdb_connection
 film film_rating
 &lt;chr&gt; &lt;chr&gt; 
 1 Toy Story G 
 2 A Bug&#039;s Life G 
 3 Toy Story 2 G 
 4 Monsters, Inc. G 
 5 Finding Nemo G 
 6 The Incredibles PG 
 7 Cars G 
 8 Ratatouille G 
 9 WALL-E G 
10 Up PG 
# … with more rows
</CODE></PRE>
]

---

# Lazy tables are not data frames

Concatenation requires `union_all()`.

.pull-left[
## Bad

```r
try(
  bind_rows(pixar_films, pixar_films)
)
```

<PRE class="fansi fansi-output"><CODE>Error in bind_rows(pixar_films, pixar_films) : 
 Argument 1 must be a data frame or a named atomic vector.
</CODE></PRE>
]

.pull-right[

## Good

```r
union_all(pixar_films, pixar_films) %>%
  arrange(release_date)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 5]
# Database: duckdb_connection
# Ordered by: release_date
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story 1995-11-22 81 G 
 2 1 Toy Story 1995-11-22 81 G 
 3 2 A Bug&#039;s Life 1998-11-25 95 G 
 4 2 A Bug&#039;s Life 1998-11-25 95 G 
 5 3 Toy Story 2 1999-11-24 92 G 
 6 3 Toy Story 2 1999-11-24 92 G 
 7 4 Monsters, Inc. 2001-11-02 92 G 
 8 4 Monsters, Inc. 2001-11-02 92 G 
 9 5 Finding Nemo 2003-05-30 100 G 
10 5 Finding Nemo 2003-05-30 100 G 
# … with more rows
</CODE></PRE>
]

---

# Lazy tables are not data frames

The bracket operator `[` doesn't work for row subsetting.

.pull-left[
## Bad

```r
try(
  pixar_films[1:3, ]
)
```

```
Error in pixar_films[1:3, ] : incorrect number of dimensions
```
]

.pull-right[
## Still bad

```r
df_pixar_films <-
 pixar_films %>%
 collect()
df_pixar_films[1:3, ]
```

<PRE class="fansi fansi-output"><CODE># A tibble: 3 × 5
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 1 Toy Story 1995-11-22 81 G 
2 2 A Bug&#039;s Life 1998-11-25 95 G 
3 3 Toy Story 2 1999-11-24 92 G 
</CODE></PRE>
]

---

# Lazy tables are set-oriented

No intrinsic order, `slice()` doesn't work.

.pull-left[
## Bad

```r
try(
  pixar_films %>%
    slice(1:3)
)
```

```
Error in slice(., 1:3) : slice() is not supported on database backends
```
]

.pull-right[
## Better

```r
pixar_films %>%
  filter(between(row_number(), 1, 3))
```

---

# Lazy tables are set-oriented

Introduce row numbering via `row_number()` (based on a specific order) as a replacement for `slice()`.

.pull-left[

```r
pixar_films %>%
  filter(between(row_number(release_date), 1, 3))
```

.pull-right[

```r
pixar_films %>%
  dbplyr::window_order(release_date) %>%
  filter(between(row_number(), 1, 3))
```

---

# Lazy tables are set-oriented

Use the `order_by` argument or `dbplyr::window_order()` (but not `arrange()`) to set the order in `cumsum()`, `lag()` etc..

.pull-left[
## Bad

```r
try(
  pixar_films %>%
    group_by(film_rating) %>%
    mutate(total_run_time = cumsum(run_time)) %>%
    ungroup()
)
```

```
Warning: Windowed expression 'SUM("run_time")' does not have explicit order.
Please use arrange() or window_order() to make determinstic.
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
 number film release_date run_time film_rating total_r…
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 1 Toy Story 1995-11-22 81 G 81
 2 2 A Bug&#039;s Life 1998-11-25 95 G 176
 3 3 Toy Story 2 1999-11-24 92 G 268
 4 4 Monsters, Inc. 2001-11-02 92 G 360
 5 5 Finding Nemo 2003-05-30 100 G 460
 6 7 Cars 2006-06-09 117 G 577
 7 8 Ratatouille 2007-06-29 111 G 688
 8 9 WALL-E 2008-06-27 98 G 786
 9 11 Toy Story 3 2010-06-18 103 G 889
10 12 Cars 2 2011-06-24 106 G 995
# … with more rows
</CODE></PRE>
]

.pull-right[

## Good

```r
pixar_films %>%
  group_by(film_rating) %>%
  dbplyr::window_order(release_date) %>%
  mutate(total_run_time = cumsum(run_time)) %>%
  ungroup()
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
# Ordered by: release_date
 number film release_date run_time film_rating total_r…
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 1 Toy Story 1995-11-22 81 G 81
 2 2 A Bug&#039;s Life 1998-11-25 95 G 176
 3 3 Toy Story 2 1999-11-24 92 G 268
 4 4 Monsters, Inc. 2001-11-02 92 G 360
 5 5 Finding Nemo 2003-05-30 100 G 460
 6 7 Cars 2006-06-09 117 G 577
 7 8 Ratatouille 2007-06-29 111 G 688
 8 9 WALL-E 2008-06-27 98 G 786
 9 11 Toy Story 3 2010-06-18 103 G 889
10 12 Cars 2 2011-06-24 106 G 995
# … with more rows
</CODE></PRE>
]

---

# Lazy tables are set-oriented

Use `arrange()` to fix the order, but only at the end of a query!

.pull-left[
## Bad

```r
pixar_films %>%
 arrange(film) %>%
 mutate(run_time_hr = run_time / 60) %>%
 filter(run_time_hr < 2)
```

<PRE class="fansi fansi-warning"><CODE>Warning: ORDER BY is ignored in subqueries without LIMIT
ℹ Do you need to move arrange() later in the pipeline or use window_order() instead?
</CODE></PRE><PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
# Ordered by: film
 number film release_date run_time film_rating run_ti…
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 1 Toy Story 1995-11-22 81 G 1.35
 2 2 A Bug&#039;s Life 1998-11-25 95 G 1.58
 3 3 Toy Story 2 1999-11-24 92 G 1.53
 4 4 Monsters, Inc. 2001-11-02 92 G 1.53
 5 5 Finding Nemo 2003-05-30 100 G 1.67
 6 6 The Incredibles 2004-11-05 115 PG 1.92
 7 7 Cars 2006-06-09 117 G 1.95
 8 8 Ratatouille 2007-06-29 111 G 1.85
 9 9 WALL-E 2008-06-27 98 G 1.63
10 10 Up 2009-05-29 96 PG 1.6 
# … with more rows
</CODE></PRE>
]

.pull-right[

## Good

```r
pixar_films %>%
 mutate(run_time_hr = run_time / 60) %>%
 filter(run_time_hr < 2) %>%
 arrange(film)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
# Ordered by: film
 number film release_date run_time film_rating run_time…
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 2 A Bug&#039;s Life 1998-11-25 95 G 1.58
 2 13 Brave 2012-06-22 93 PG 1.55
 3 7 Cars 2006-06-09 117 G 1.95
 4 12 Cars 2 2011-06-24 106 G 1.77
 5 18 Cars 3 2017-06-16 102 G 1.7 
 6 19 Coco 2017-11-22 105 PG 1.75
 7 17 Finding Dory 2016-06-17 97 PG 1.62
 8 5 Finding Nemo 2003-05-30 100 G 1.67
 9 20 Incredibles 2 2018-06-15 118 PG 1.97
10 15 Inside Out 2015-06-19 95 PG 1.58
# … with more rows
</CODE></PRE>
]

---

# Sort order of missing values

`NA` sort last in data frames, but first on databases.

.pull-left[

```r
pixar_films %>%
  arrange(run_time)
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;pixar_films&gt; [?? x 5]
# Database: duckdb_connection
# Ordered by: run_time
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 25 Turning Red 2022-03-11 NA N/A 
 2 26 Lightyear 2022-06-17 NA N/A 
 3 1 Toy Story 1995-11-22 81 G 
 4 3 Toy Story 2 1999-11-24 92 G 
 5 4 Monsters, Inc. 2001-11-02 92 G 
 6 13 Brave 2012-06-22 93 PG 
 7 16 The Good Dinosaur 2015-11-25 93 PG 
 8 2 A Bug&#039;s Life 1998-11-25 95 G 
 9 15 Inside Out 2015-06-19 95 PG 
10 10 Up 2009-05-29 96 PG 
# … with more rows
</CODE></PRE>
]

.pull-right[

```r
pixar_films %>%
  arrange(is.na(run_time), run_time)
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;pixar_films&gt; [?? x 5]
# Database: duckdb_connection
# Ordered by: is.na(run_time), run_time
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story 1995-11-22 81 G 
 2 3 Toy Story 2 1999-11-24 92 G 
 3 4 Monsters, Inc. 2001-11-02 92 G 
 4 13 Brave 2012-06-22 93 PG 
 5 16 The Good Dinosaur 2015-11-25 93 PG 
 6 2 A Bug&#039;s Life 1998-11-25 95 G 
 7 15 Inside Out 2015-06-19 95 PG 
 8 10 Up 2009-05-29 96 PG 
 9 17 Finding Dory 2016-06-17 97 PG 
10 9 WALL-E 2008-06-27 98 G 
# … with more rows
</CODE></PRE>
]

---

# Logical/Boolean data type

Support differs across databases.

.pull-left[
## DuckDB

```r
pixar_films %>%
  count(film_rating == "PG")
```

.pull-right[
## SQLite

```r
pixar_films_sqlite %>%
  count(film_rating == "PG")
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 2]
# Database: sqlite 3.37.2 []
 `film_rating == &quot;PG&quot;` n
 &lt;int&gt; &lt;int&gt;
1 0 17
2 1 10
</CODE></PRE>
]

---

# Logical/Boolean data type

Use `if_else()` or `case_when()` if in doubt.

.pull-left[
## SQLite

```r
pixar_films_sqlite %>%
  count(film_rating == "PG")
```

.pull-right[
## SQL Server (universal)

```r
pixar_films_sqlite %>%
  count(if_else(film_rating == "PG", 1L, 0L))
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 2]
# Database: sqlite 3.37.2 []
 `if_else(film_rating == &quot;PG&quot;, 1L, 0L)` n
 &lt;int&gt; &lt;int&gt;
1 0 17
2 1 10
</CODE></PRE>
]

---

# Imperfect translations

Numeric values always use a decimal point.

.pull-left[
## Numeric

```r
pixar_films %>%
 filter(run_time < 120) %>%
 show_query()
```

```
<SQL>
SELECT *
FROM "pixar_films"
WHERE ("run_time" < 120.0)
```
]

.pull-right[
## Integer

```r
pixar_films %>%
 filter(run_time < 120L) %>%
 show_query()
```

```
<SQL>
SELECT *
FROM "pixar_films"
WHERE ("run_time" < 120)
```
]

---

# Imperfect translations

Not all R functions are supported.

.pull-left[

```r
try(
  pixar_films %>%
    filter(grepl("^Toy", film)) %>%
    print()
)
```

```
Error : duckdb_prepare_R: Failed to prepare query SELECT *
FROM "pixar_films"
WHERE (grepl('^Toy', "film"))
LIMIT 11
Error: Catalog Error: Scalar Function with name grepl does not exist!
Did you mean "ceil"?
LINE 3: WHERE (grepl('^Toy', "film"))
               ^
```
]

.pull-right[

```r
try(
  pixar_films %>%
    filter(str_detect(film, "^Toy")) %>%
    print()
)
```

```
Error : str_detect() is not available in this SQL variant
```
]

---

# Imperfect translations

Unknown functions and operators are passed on verbatim.

.pull-left[
## Lazy table

```r
pixar_films %>%
  filter(film %LIKE% "Toy%")
```

.pull-right[
## Data frame

```r
try(
  pixar_films %>%
    collect() %>%
    filter(film %LIKE% "Toy%")
)
```

<PRE class="fansi fansi-output"><CODE>Error in filter(., film %LIKE% &quot;Toy%&quot;) : 
 Problem while computing `..1 = film %LIKE% &quot;Toy%&quot;`.
Caused by error in `film %LIKE% &quot;Toy%&quot;`:
! could not find function &quot;%LIKE%&quot;
</CODE></PRE>
]

---

# Imperfect translations

Unknown functions and operators are passed on verbatim.

.pull-left[
## Lazy table

```r
pixar_films %>%
  summarize(MAX(run_time))
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 1]
# Database: duckdb_connection
 `MAX(run_time)`
 &lt;dbl&gt;
1 155
</CODE></PRE>
]

.pull-right[
## Data frame

```r
try(
  pixar_films %>%
    collect() %>%
    summarize(MAX(run_time))
)
```

<PRE class="fansi fansi-output"><CODE>Error in summarize(., MAX(run_time)) : 
 Problem while computing `..1 = MAX(run_time)`.
Caused by error in `MAX()`:
! could not find function &quot;MAX&quot;
</CODE></PRE>
]

---

# Imperfect translations

Aggregation functions don't support `na.rm = FALSE` (the default in R).

.pull-left[
## Warning

```r
pixar_films %>%
  summarize(total_run_time = sum(run_time)) %>%
  show_query()
```

```
Warning: Missing values are always removed in SQL.
Use `SUM(x, na.rm = TRUE)` to silence this warning
This warning is displayed only once per session.
```

```
<SQL>
SELECT SUM("run_time") AS "total_run_time"
FROM "pixar_films"
```
]

.pull-right[
## Hackaround

```r
pixar_films %>%
  summarize(total_run_time = SUM(run_time)) %>%
  show_query()
```

```
<SQL>
SELECT SUM("run_time") AS "total_run_time"
FROM "pixar_films"
```
]

---

# Imperfect translations

Escape hatch: `sql()` to pass through arbitrary SQL.

.pull-left[
## Fine-grained

```r
pixar_films %>%
  mutate(number = CAST(number %AS% sql("integer")))
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 5]
# Database: duckdb_connection
 number film release_date run_time film_rating
 &lt;int&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story 1995-11-22 81 G 
 2 2 A Bug&#039;s Life 1998-11-25 95 G 
 3 3 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. 2001-11-02 92 G 
 5 5 Finding Nemo 2003-05-30 100 G 
 6 6 The Incredibles 2004-11-05 115 PG 
 7 7 Cars 2006-06-09 117 G 
 8 8 Ratatouille 2007-06-29 111 G 
 9 9 WALL-E 2008-06-27 98 G 
10 10 Up 2009-05-29 96 PG 
# … with more rows
</CODE></PRE>
]

.pull-right[
## Everything

```r
pixar_films %>%
  mutate(number = CAST(number %AS% sql("integer")))
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 5]
# Database: duckdb_connection
 number film release_date run_time film_rating
 &lt;int&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story 1995-11-22 81 G 
 2 2 A Bug&#039;s Life 1998-11-25 95 G 
 3 3 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. 2001-11-02 92 G 
 5 5 Finding Nemo 2003-05-30 100 G 
 6 6 The Incredibles 2004-11-05 115 PG 
 7 7 Cars 2006-06-09 117 G 
 8 8 Ratatouille 2007-06-29 111 G 
 9 9 WALL-E 2008-06-27 98 G 
10 10 Up 2009-05-29 96 PG 
# … with more rows
</CODE></PRE>
]

---

# Imperfect translations

On the up side: `sql()` is rarely needed.

.pull-left[
## Manual

```r
pixar_films %>%
  transmute(number = CAST(number %AS% sql("integer"))) %>%
  show_query()
```

```
<SQL>
SELECT CAST("number" AS integer) AS "number"
FROM "pixar_films"
```
]

.pull-right[
## Translated

```r
pixar_films %>%
  transmute(number = as.integer(number)) %>% 
  show_query()
```

```
<SQL>
SELECT CAST("number" AS INTEGER) AS "number"
FROM "pixar_films"
```
]

---

# Lazy tables are recomputed on every access

Saving to a variable doesn't help.

.pull-left[

```r
films_two_per_year <-
 pixar_films %>%
 transmute(film, y = year(release_date)) %>% 
 add_count(y) %>%
 filter(n > 1)

films_two_per_year %>%
  show_query()
```

```
<SQL>
SELECT *
FROM (SELECT "film", "y", COUNT(*) OVER (PARTITION BY "y") AS "n"
FROM (SELECT "film", EXTRACT(year FROM "release_date") AS "y"
FROM "pixar_films") "q01") "q02"
WHERE ("n" > 1.0)
```
]

.pull-right[

```r
films_two_per_year %>%
  arrange(release_date) %>%
  show_query()
```

---

# Materialize temporary results

Remedy: materialize with `compute()`. Requires write access to the database (at least for temporary tables).

.pull-left[

```r
films_two_per_year_mat <-
 films_two_per_year %>%
 compute(unique_indexes = list(c("film")))

films_two_per_year_mat
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;dbplyr_001&gt; [?? x 3]
# Database: duckdb_connection
# Groups: y
 film y n
 &lt;chr&gt; &lt;dbl&gt; &lt;dbl&gt;
1 Turning Red 2022 2
2 Lightyear 2022 2
3 Onward 2020 2
4 Soul 2020 2
5 Cars 3 2017 2
6 Coco 2017 2
7 Inside Out 2015 2
8 The Good Dinosaur 2015 2
</CODE></PRE>
]

.pull-right[

```r
films_two_per_year_mat %>%
  arrange(release_date) %>%
  show_query()
```

```
<SQL>
SELECT *
FROM "dbplyr_001"
ORDER BY "release_date"
```
]

---

background-image: url("data:image/png;base64,#images/14-frame.webp")
background-size: 40%
background-position: 100% 100%

# Exercises

1. Experiment.

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- **Joins**
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/21.webp")
background-size: 40%
background-position: 100% 100%

# Joins

.pull-left[
- Usage
- Join sources
- Mounting

Script: `databases_21.R`

```r
library(tidyverse)
```
]

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: 40%
background-position: 100% 100%

# Joins

.pull-left[
- Usage
- Join sources
- Mounting

Script: `databases_21.R`

```r
library(tidyverse)
```
]

---

# A second table

.pull-left[

```r
academy <- tbl(con_duckdb, "academy")
academy_sqlite <- tbl(con_sqlite, "academy")

academy %>%
  count(status)
```

.pull-right[

```r
academy
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;academy&gt; [?? x 3]
# Database: duckdb_connection
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
 1 Toy Story Animated Feature Award not yet introduced
 2 Toy Story Original Screenplay Nominated 
 3 Toy Story Adapted Screenplay Ineligible 
 4 Toy Story Original Score Nominated 
 5 Toy Story Original Song Nominated 
 6 Toy Story Other Won Special Achievement 
 7 A Bug&#039;s Life Animated Feature Award not yet introduced
 8 A Bug&#039;s Life Adapted Screenplay Ineligible 
 9 A Bug&#039;s Life Original Score Nominated 
10 Toy Story 2 Animated Feature Award not yet introduced
# … with more rows
</CODE></PRE>
]

---

# Left join

The most frequent kind of join.

.pull-left[
## Unsafe

```r
academy %>%
  left_join(pixar_films)
```

<PRE class="fansi fansi-message"><CODE>Joining, by = &quot;film&quot;
</CODE></PRE><PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 7]
# Database: duckdb_connection
 film award_type status number release_d… run_t… film_…
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Fe… Award… 1 1995-11-22 81 G 
 2 Toy Story Original Sc… Nomin… 1 1995-11-22 81 G 
 3 Toy Story Adapted Scr… Ineli… 1 1995-11-22 81 G 
 4 Toy Story Original Sc… Nomin… 1 1995-11-22 81 G 
 5 Toy Story Original So… Nomin… 1 1995-11-22 81 G 
 6 Toy Story Other Won S… 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Fe… Award… 2 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Scr… Ineli… 2 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Sc… Nomin… 2 1998-11-25 95 G 
10 Toy Story 2 Animated Fe… Award… 3 1999-11-24 92 G 
# … with more rows
</CODE></PRE>
]

.pull-right[
## Better

```r
academy %>%
  left_join(pixar_films, by = "film")
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 7]
# Database: duckdb_connection
 film award_type status number release_d… run_t… film_…
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Fe… Award… 1 1995-11-22 81 G 
 2 Toy Story Original Sc… Nomin… 1 1995-11-22 81 G 
 3 Toy Story Adapted Scr… Ineli… 1 1995-11-22 81 G 
 4 Toy Story Original Sc… Nomin… 1 1995-11-22 81 G 
 5 Toy Story Original So… Nomin… 1 1995-11-22 81 G 
 6 Toy Story Other Won S… 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Fe… Award… 2 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Scr… Ineli… 2 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Sc… Nomin… 2 1998-11-25 95 G 
10 Toy Story 2 Animated Fe… Award… 3 1999-11-24 92 G 
# … with more rows
</CODE></PRE>
]

---

# Left join

Computed on the database, original data unchanged.

```r
academy %>%
  left_join(pixar_films, by = "film") %>%
  show_query()
```

```
<SQL>
SELECT "LHS"."film" AS "film", "award_type", "status", "number", "release_date", "run_time", "film_rating"
FROM "academy" AS "LHS"
LEFT JOIN "pixar_films" AS "RHS"
ON ("LHS"."film" = "RHS"."film")
```

---

# Join with preparation

The right-hand side in the join should come from a variable.

.pull-left[
## Prepare RHS

```r
academy_won <-
 academy %>%
 filter(status == "Won") %>%
 count(film, name = "n_won")
academy_won
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 2]
# Database: duckdb_connection
 film n_won
 &lt;chr&gt; &lt;dbl&gt;
 1 Monsters, Inc. 1
 2 Finding Nemo 1
 3 The Incredibles 2
 4 Ratatouille 1
 5 WALL-E 1
 6 Up 2
 7 Toy Story 3 2
 8 Brave 1
 9 Inside Out 1
10 Coco 2
# … with more rows
</CODE></PRE>
]

.pull-right[

```r
pixar_films %>%
  left_join(academy_won, by = "film")
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
 number film release_date run_time film_rating n_won
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 4 Monsters, Inc. 2001-11-02 92 G 1
 2 5 Finding Nemo 2003-05-30 100 G 1
 3 6 The Incredibles 2004-11-05 115 PG 2
 4 8 Ratatouille 2007-06-29 111 G 1
 5 9 WALL-E 2008-06-27 98 G 1
 6 10 Up 2009-05-29 96 PG 2
 7 11 Toy Story 3 2010-06-18 103 G 2
 8 13 Brave 2012-06-22 93 PG 1
 9 15 Inside Out 2015-06-19 95 PG 1
10 19 Coco 2017-11-22 105 PG 2
# … with more rows
</CODE></PRE>
]

---

# Join with postprocessing

.pull-left[
## Raw result

```r
pixar_films %>%
  left_join(academy_won, by = "film") %>%
  arrange(release_date)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
# Ordered by: release_date
 number film release_date run_time film_rating n_won
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 1 Toy Story 1995-11-22 81 G NA
 2 2 A Bug&#039;s Life 1998-11-25 95 G NA
 3 3 Toy Story 2 1999-11-24 92 G NA
 4 4 Monsters, Inc. 2001-11-02 92 G 1
 5 5 Finding Nemo 2003-05-30 100 G 1
 6 6 The Incredibles 2004-11-05 115 PG 2
 7 7 Cars 2006-06-09 117 G NA
 8 8 Ratatouille 2007-06-29 111 G 1
 9 9 WALL-E 2008-06-27 98 G 1
10 10 Up 2009-05-29 96 PG 2
# … with more rows
</CODE></PRE>
]

.pull-right[
## After postprocessing

```r
pixar_films %>%
  left_join(academy_won, by = "film") %>%
  mutate(n_won = coalesce(n_won, 0L)) %>%
  arrange(release_date)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 6]
# Database: duckdb_connection
# Ordered by: release_date
 number film release_date run_time film_rating n_won
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;dbl&gt;
 1 1 Toy Story 1995-11-22 81 G 0
 2 2 A Bug&#039;s Life 1998-11-25 95 G 0
 3 3 Toy Story 2 1999-11-24 92 G 0
 4 4 Monsters, Inc. 2001-11-02 92 G 1
 5 5 Finding Nemo 2003-05-30 100 G 1
 6 6 The Incredibles 2004-11-05 115 PG 2
 7 7 Cars 2006-06-09 117 G 0
 8 8 Ratatouille 2007-06-29 111 G 1
 9 9 WALL-E 2008-06-27 98 G 1
10 10 Up 2009-05-29 96 PG 2
# … with more rows
</CODE></PRE>
]

---

# Join with processing

Computed on the database, original data unchanged.

```r
pixar_films %>%
  left_join(academy_won, by = "film") %>%
  mutate(n_won = coalesce(n_won, 0L)) %>%
  arrange(release_date) %>%
  show_query()
```

```
<SQL>
SELECT "number", "film", "release_date", "run_time", "film_rating", COALESCE("n_won", 0) AS "n_won"
FROM (SELECT "number", "LHS"."film" AS "film", "release_date", "run_time", "film_rating", "n_won"
FROM "pixar_films" AS "LHS"
LEFT JOIN (SELECT "film", COUNT(*) AS "n_won"
FROM "academy"
WHERE ("status" = 'Won')
GROUP BY "film") "RHS"
ON ("LHS"."film" = "RHS"."film")
) "q01"
ORDER BY "release_date"
```

---

# Tables must be on the same source

Use `copy = TRUE` to enforce, the result is a lazy table if the LHS is a lazy table.

.pull-left[
## Bad

```r
try(
  academy %>%
    left_join(pixar_films_sqlite, by = "film")
)
```

<PRE class="fansi fansi-output"><CODE>Error in auto_copy(x, y, copy = copy, indexes = if (auto_index) list(by$y)) : 
 `x` and `y` must share the same src.
ℹ set `copy` = TRUE (may be slow).
</CODE></PRE>
]

.pull-right[
## Not too bad

```r
academy %>%
  left_join(pixar_films_sqlite, by = "film", copy = TRUE)
```

---

# Copying is expensive!

A temporary table is created on the LHS database.
If the RHS comes from a different database, results are temporarily loaded into the local session!

```r
academy %>%
  left_join(pixar_films_sqlite, by = "film", copy = TRUE) %>%
  show_query()
```

```
<SQL>
SELECT "LHS"."film" AS "film", "award_type", "status", "number", "release_date", "run_time", "film_rating"
FROM "academy" AS "LHS"
LEFT JOIN "dbplyr_002" AS "RHS"
ON ("LHS"."film" = "RHS"."film")
```

---

# Joining data frames with lazy tables

The result is a data frame too.

.pull-left[
## Bad

```r
try(
  pixarfilms::academy %>%
    left_join(pixar_films, by = "film")
)
```

<PRE class="fansi fansi-output"><CODE>Error in auto_copy(x, y, copy = copy) : 
 `x` and `y` must share the same src.
ℹ set `copy` = TRUE (may be slow).
</CODE></PRE>
]

.pull-right[
## Could be worse

```r
pixarfilms::academy %>%
  left_join(pixar_films, by = "film", copy = TRUE)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 80 × 7
 film award_type status number release_d… run_t… film_…
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Fe… Award… 1 1995-11-22 81 G 
 2 Toy Story Original Sc… Nomin… 1 1995-11-22 81 G 
 3 Toy Story Adapted Scr… Ineli… 1 1995-11-22 81 G 
 4 Toy Story Original Sc… Nomin… 1 1995-11-22 81 G 
 5 Toy Story Original So… Nomin… 1 1995-11-22 81 G 
 6 Toy Story Other Won S… 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Fe… Award… 2 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Scr… Ineli… 2 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Sc… Nomin… 2 1998-11-25 95 G 
10 Toy Story 2 Animated Fe… Award… 3 1999-11-24 92 G 
# … with 70 more rows
</CODE></PRE>
]

---

# DuckDB: register data frames as database tables

Temporarily use a local data frame as a table.
Also works for Arrow datasets via `duckdb::duckdb_register_arrow()`.

.pull-left[
## Register and access

```r
duckdb::duckdb_register(
  con_duckdb,
  "academy_small",
  pixarfilms::academy[1:3, ]
)

academy_small <- tbl(con_duckdb, "academy_small")
academy_small
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;academy_small&gt; [?? x 3]
# Database: duckdb_connection
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
1 Toy Story Animated Feature Award not yet introduced
2 Toy Story Original Screenplay Nominated 
3 Toy Story Adapted Screenplay Ineligible 
</CODE></PRE>
]

.pull-right[
## Use

```r
academy_small %>%
  left_join(pixar_films, by = "film")
```

---

# DuckDB: Performance comparison

Baseline: Data frames.

.pull-left[

```r
nrow(nycflights13::flights)
```

```
[1] 336776
```
]

.pull-right[

```r
system.time(
  nycflights13::flights %>%
    count(year, month, day)
)
```

```
   user  system elapsed 
  0.010   0.000   0.011 
```
]

---

# DuckDB: Performance comparison

With registration.

.pull-left[

```r
system.time(duckdb::duckdb_register(
  con_duckdb,
  "flights",
  nycflights13::flights
))
```

```
   user  system elapsed 
  0.003   0.000   0.003 
```

```r
flights_register <- tbl(con_duckdb, "flights")
flights_register %>%
 count()
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 1]
# Database: duckdb_connection
 n
 &lt;dbl&gt;
1 336776
</CODE></PRE>
]

.pull-right[

```r
system.time(
  flights_register %>%
    count(year, month, day) %>%
    collect()
)
```

```
   user  system elapsed 
  0.032   0.003   0.038 
```
]

---

# DuckDB: Performance comparison

With copy.

.pull-left[

```r
system.time(
 flights_copy <- copy_to(con_duckdb, nycflights13::flights)
)
```

```
   user  system elapsed 
  0.090   0.014   0.116 
```

```r
flights_copy %>%
  count()
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 1]
# Database: duckdb_connection
 n
 &lt;dbl&gt;
1 336776
</CODE></PRE>
]

.pull-right[

```r
system.time(
  flights_copy %>%
    count(year, month, day) %>%
    collect()
)
```

```
   user  system elapsed 
  0.008   0.001   0.007 
```
]

---

# ETL, revisited

Insert a second table into our database.

```r
db_path <- fs::path_abs("pixar.duckdb")
con <- DBI::dbConnect(duckdb::duckdb(dbdir = db_path))
DBI::dbWriteTable(con, "academy", pixarfilms::academy, overwrite = TRUE)
DBI::dbExecute(con, "CREATE UNIQUE INDEX academy_pk ON academy (film, award_type)")
```

```
[1] 0
```

```r
DBI::dbExecute(con, "CREATE INDEX academy_fk ON academy (film)")
```

```
[1] 0
```

```r
DBI::dbDisconnect(con)
```

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: 40%
background-position: 100% 100%

# Joins: Exercises 1

.pull-left[
1. How many rows does the join between `academy` and `pixar_films` contain?
   Try to find out without loading all the data into memory. Explain.
2. Which films are not yet listed in the `academy` table? What does the
   resulting SQL query look like?
   - Hint: Use `anti_join()`
]

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: 40%
background-position: 100% 100%

# Joins: Exercises 2

.pull-left[
3. Transform `academy` into a wide table so that there is at most one row
   per film. Join the resulting table with the `pixar_films` table.
   - Hint: Use `pivot_wider()`, `spread()`, `dcast()`, ... . You need to
     compute locally, because these functions don't work on the database.
]

.pull-right[
4. Plot a bar chart with the number of awards won and nominated per year.
   Compute as much as possible on the database.
   - Hint: "Long form" or "wide form"?
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- **The {dm} package**
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/22.webp")
background-size: 40%
background-position: 100% 100%

# Data model basics

.pull-left[
- Compound object for multiple tables
- Features

Script: `databases_22.R`

```r
library(tidyverse)
library(dm)
```
]

---

background-image: url("data:image/png;base64,#images/22-frame.webp")
background-size: 40%
background-position: 100% 100%

# Data model basics

.pull-left[
- Compound object for multiple tables
- Features

Script: `databases_22.R`

```r
library(tidyverse)
library(dm)
```
]

---

# Data model objects

.pull-left[
Store multiple tables in an object.

```r
pixar_dm <- dm_pixarfilms()
pixar_dm
```

<PRE class="fansi fansi-output"><CODE>── Metadata ───────────────────────────────────────────────────────
Tables: `pixar_films`, `pixar_people`, `academy`, `box_office`, `genres`, `public_response`
Columns: 23
Primary keys: 5
Foreign keys: 5
</CODE></PRE>

```r
pixar_dm %>%
  dm_draw()
```
]

.pull-right[


<svg width="315pt" height="330pt"
 viewBox="-100.00 0.00 315.00 330.00" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
<g id="graph0" class="graph" transform="scale(1 1) rotate(0) translate(4 326)">
<title>%0</title>
<g id="a_graph0"><a xlink:title="Data Model">
<polygon fill="#ffffff" stroke="transparent" points="-4,4 -4,-326 211,-326 211,4 -4,4"/>
</a>
</g>

<g id="node1" class="node">
<title>academy</title>
<polygon fill="#ed7d31" stroke="transparent" points="1.5,-301 1.5,-321 100.5,-321 100.5,-301 1.5,-301"/>
<text text-anchor="start" x="26.1255" y="-306.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">academy</text>
<polygon fill="#fbe5d5" stroke="transparent" points="1.5,-281 1.5,-301 100.5,-301 100.5,-281 1.5,-281"/>
<text text-anchor="start" x="3.5" y="-286.4" font-family="Times,serif" font-size="14.00" fill="#444444">film</text>
<polygon fill="#fbe5d5" stroke="transparent" points="1.5,-261 1.5,-281 100.5,-281 100.5,-261 1.5,-261"/>
<text text-anchor="start" x="3.183" y="-267.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film, award_type</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="0,-260 0,-322 101,-322 101,-260 0,-260"/>
</g>

<g id="node4" class="node">
<title>pixar_films</title>
<polygon fill="#5b9bd5" stroke="transparent" points="138,-161 138,-181 206,-181 206,-161 138,-161"/>
<text text-anchor="start" x="139.7286" y="-166.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">pixar_films</text>
<polygon fill="#deebf6" stroke="transparent" points="138,-141 138,-161 206,-161 206,-141 138,-141"/>
<text text-anchor="start" x="140" y="-147.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#3c678e" stroke-opacity="0.666667" points="137,-140 137,-182 207,-182 207,-140 137,-140"/>
</g>

<g id="edge2" class="edge">
<title>academy:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M100.5,-291C161.0155,-291 84.073,-167.4399 127.9432,-152.4775"/>
<polygon fill="#555555" stroke="#555555" points="128.615,-155.9165 138,-151 127.5974,-148.9909 128.615,-155.9165"/>
</g>

<g id="node2" class="node">
<title>box_office</title>
<polygon fill="#ed7d31" stroke="transparent" points="18.5,-221 18.5,-241 82.5,-241 82.5,-221 18.5,-221"/>
<text text-anchor="start" x="20.1795" y="-226.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">box_office</text>
<polygon fill="#fbe5d5" stroke="transparent" points="18.5,-201 18.5,-221 82.5,-221 82.5,-201 18.5,-201"/>
<text text-anchor="start" x="20.5" y="-207.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="17.5,-200 17.5,-242 83.5,-242 83.5,-200 17.5,-200"/>
</g>

<g id="edge3" class="edge">
<title>box_office:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M82.5,-211C114.9945,-211 104.6973,-162.9888 128.1654,-152.8615"/>
<polygon fill="#555555" stroke="#555555" points="128.8254,-156.2988 138,-151 127.5235,-149.4209 128.8254,-156.2988"/>
</g>

<g id="node3" class="node">
<title>genres</title>
<polygon fill="#ed7d31" stroke="transparent" points="18.5,-161 18.5,-181 83.5,-181 83.5,-161 18.5,-161"/>
<text text-anchor="start" x="32.7328" y="-166.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">genres</text>
<polygon fill="#fbe5d5" stroke="transparent" points="18.5,-141 18.5,-161 83.5,-161 83.5,-141 18.5,-141"/>
<text text-anchor="start" x="20.5" y="-146.4" font-family="Times,serif" font-size="14.00" fill="#444444">film</text>
<polygon fill="#fbe5d5" stroke="transparent" points="18.5,-121 18.5,-141 83.5,-141 83.5,-121 18.5,-121"/>
<text text-anchor="start" x="20.2889" y="-127.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film, genre</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="17,-120 17,-182 84,-182 84,-120 17,-120"/>
</g>

<g id="edge4" class="edge">
<title>genres:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M83.5,-151C103.9375,-151 111.4419,-151 127.8378,-151"/>
<polygon fill="#555555" stroke="#555555" points="128,-154.5001 138,-151 128,-147.5001 128,-154.5001"/>
</g>

<g id="node5" class="node">
<title>pixar_people</title>
<polygon fill="#70ad47" stroke="transparent" points="12.5,-81 12.5,-101 89.5,-101 89.5,-81 12.5,-81"/>
<text text-anchor="start" x="14.4572" y="-86.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">pixar_people</text>
<polygon fill="#e2eeda" stroke="transparent" points="12.5,-61 12.5,-81 89.5,-81 89.5,-61 12.5,-61"/>
<text text-anchor="start" x="14.5" y="-66.4" font-family="Times,serif" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#4a732f" stroke-opacity="0.666667" points="11,-60 11,-102 90,-102 90,-60 11,-60"/>
</g>

<g id="edge1" class="edge">
<title>pixar_people:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M89.5,-71C127.1813,-71 102.2491,-136.7031 128.1904,-149.0225"/>
<polygon fill="#555555" stroke="#555555" points="127.5055,-152.4547 138,-151 128.8889,-145.5928 127.5055,-152.4547"/>
</g>

<g id="node6" class="node">
<title>public_response</title>
<polygon fill="#ed7d31" stroke="transparent" points="3.5,-21 3.5,-41 97.5,-41 97.5,-21 3.5,-21"/>
<text text-anchor="start" x="5.0126" y="-26.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">public_response</text>
<polygon fill="#fbe5d5" stroke="transparent" points="3.5,-1 3.5,-21 97.5,-21 97.5,-1 3.5,-1"/>
<text text-anchor="start" x="5.5" y="-7.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="2.5,0 2.5,-42 98.5,-42 98.5,0 2.5,0"/>
</g>

<g id="edge5" class="edge">
<title>public_response:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M97.5,-11C158.3517,-11 83.4456,-134.5601 127.8579,-149.5225"/>
<polygon fill="#555555" stroke="#555555" points="127.5999,-153.0217 138,-151 128.6091,-146.0949 127.5999,-153.0217"/>
</g>
</g>
</svg>
]

---

# Data model objects

Use like a named list.

```r
names(pixar_dm)
```

```
[1] "pixar_films"     "pixar_people"    "academy"         "box_office"      "genres"          "public_response"
```

.pull-left[

```r
pixar_dm$pixar_films
```

.pull-right[

```r
pixar_dm$academy
```

<PRE class="fansi fansi-output"><CODE># A tibble: 80 × 3
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
 1 Toy Story Animated Feature Award not yet introduced
 2 Toy Story Original Screenplay Nominated 
 3 Toy Story Adapted Screenplay Ineligible 
 4 Toy Story Original Score Nominated 
 5 Toy Story Original Song Nominated 
 6 Toy Story Other Won Special Achievement 
 7 A Bug&#039;s Life Animated Feature Award not yet introduced
 8 A Bug&#039;s Life Adapted Screenplay Ineligible 
 9 A Bug&#039;s Life Original Score Nominated 
10 Toy Story 2 Animated Feature Award not yet introduced
# … with 70 more rows
</CODE></PRE>
]

---

# Showcase: wrapping all tables in a data model

One of the many operations supported by {dm}.

.pull-left[

```r
pixar_films_wrapped <-
 pixar_dm %>%
 dm_wrap_tbl(pixar_films) %>%
 pull_tbl(pixar_films)

pixar_films_wrapped
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 10
 number film release_d… run_t… film_… pixar_p… academy box_off…
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; &lt;nested&gt; &lt;nested&gt; &lt;nested&gt;
 1 1 Toy … 1995-11-22 81 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 2 2 A Bu… 1998-11-25 95 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 3 3 Toy … 1999-11-24 92 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 4 4 Mons… 2001-11-02 92 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 5 5 Find… 2003-05-30 100 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 6 6 The … 2004-11-05 115 PG &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 7 7 Cars 2006-06-09 117 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 8 8 Rata… 2007-06-29 111 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
 9 9 WALL… 2008-06-27 98 G &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
10 10 Up 2009-05-29 96 PG &lt;tibble&gt; &lt;tibble&gt; &lt;tibble&gt;
# … with 17 more rows, and 2 more variables: genres &lt;nested&gt;,
# public_response &lt;nested&gt;
</CODE></PRE>
]

.pull-right[

```r
pixar_films_wrapped$academy[1:2]
```

<PRE class="fansi fansi-output"><CODE>[[1]]
# A tibble: 6 × 2
 award_type status 
 &lt;chr&gt; &lt;chr&gt; 
1 Animated Feature Award not yet introduced
2 Original Screenplay Nominated 
3 Adapted Screenplay Ineligible 
4 Original Score Nominated 
5 Original Song Nominated 
6 Other Won Special Achievement

[[2]]
# A tibble: 3 × 2
 award_type status 
 &lt;chr&gt; &lt;chr&gt; 
1 Animated Feature Award not yet introduced
2 Adapted Screenplay Ineligible 
3 Original Score Nominated 
</CODE></PRE>
]

---

background-image: url("data:image/png;base64,#images/22-frame.webp")
background-size: 40%
background-position: 100% 100%

# Data model basics: Exercises

.pull-left[
- Experiment!
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- **A bit of theory**
- Playing the whole game
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/23.webp")
background-size: 40%
background-position: 100% 100%

# Data models

.pull-left[
- Keys, relationships, constraints
- Zooming

Script: `databases_23.R`

```r
library(tidyverse)
library(dm)
```
]

---

background-image: url("data:image/png;base64,#images/23-frame.webp")
background-size: 40%
background-position: 100% 100%

# Data models

.pull-left[
- Keys, relationships, constraints
- Zooming

Script: `databases_23.R`

```r
library(tidyverse)
library(dm)
```
]

---

# Primary keys

Column(s) that uniquely identify rows in a table.

.pull-left[

```r
any(duplicated(pixar_dm$pixar_films$film))
```

```
[1] FALSE
```

```r
check_key(pixar_dm$pixar_films, film)
```
]

.pull-right[

```r
any(duplicated(pixar_dm$academy[c("film", "award_type")]))
```

```
[1] FALSE
```

```r
check_key(pixar_dm$academy, film, award_type)
try(
  check_key(pixar_dm$academy, film)
)
```

```
Error in abort_not_unique_key(as_label(data_q), orig_names) : 
  (`film`) not a unique key of `pixar_dm$academy`.
```
]

---

# Foreign keys

Column(s) that point to a primary key in another table.

```r
all(pixar_dm$academy$film %in% pixar_dm$pixar_films$film)
```

```
[1] TRUE
```

```r
check_subset(pixar_dm$academy, film, pixar_dm$pixar_films, film)
try(
  check_subset(pixar_dm$pixar_films, film, pixar_dm$academy, film)
)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 4 × 5
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 24 Luca 2021-06-18 151 N/A 
2 25 Turning Red 2022-03-11 NA N/A 
3 26 Lightyear 2022-06-17 NA N/A 
4 27 NA 2023-06-16 155 Not Rated 
Error in abort_not_subset_of(as_label(t1q), col_names_1, as_label(t2q), : 
 Column (`film`) of table `pixar_dm$pixar_films` contains values (see examples above) that are not present in column (`film`) of table `pixar_dm$academy`.
</CODE></PRE>

---

# Constraints

Properties of primary and foreign keys can be checked.

```r
pixar_dm %>%
  dm_examine_constraints()
```

<PRE class="fansi fansi-message"><CODE>! Unsatisfied constraints:
</CODE></PRE><PRE class="fansi fansi-output"><CODE>• Table `pixar_films`: primary key `film`: has 1 missing values
</CODE></PRE>

```r
dm_pixarfilms(consistent = TRUE) %>%
  dm_examine_constraints()
```

<PRE class="fansi fansi-message"><CODE>ℹ All constraints satisfied.
</CODE></PRE>

---

# Constraints

An example from another dataset.

```r
dm_nycflights13() %>%
  dm_examine_constraints()
```

<PRE class="fansi fansi-message"><CODE>! Unsatisfied constraints:
</CODE></PRE><PRE class="fansi fansi-output"><CODE>• Table `flights`: foreign key `tailnum` into table `planes`: values of `flights$tailnum` not in `planes$tailnum`: N725MQ (6), N537MQ (5), N722MQ (5), N730MQ (5), N736MQ (5), …
</CODE></PRE>

---

# Zooming

Focusing on one table in a dm object.
Allows applying data transformations on that table inside a dm object.

.pull-left[

```r
pixar_dm %>%
  dm_zoom_to(academy)
```

<PRE class="fansi fansi-output"><CODE># Zoomed table: academy
# A tibble: 80 × 3
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
 1 Toy Story Animated Feature Award not yet introduced
 2 Toy Story Original Screenplay Nominated 
 3 Toy Story Adapted Screenplay Ineligible 
 4 Toy Story Original Score Nominated 
 5 Toy Story Original Song Nominated 
 6 Toy Story Other Won Special Achievement 
 7 A Bug&#039;s Life Animated Feature Award not yet introduced
 8 A Bug&#039;s Life Adapted Screenplay Ineligible 
 9 A Bug&#039;s Life Original Score Nominated 
10 Toy Story 2 Animated Feature Award not yet introduced
# … with 70 more rows
</CODE></PRE>
]

.pull-right[

```r
pixar_dm %>%
  dm_zoom_to(academy) %>%
  left_join(pixar_films, select = c(film, release_date))
```

<PRE class="fansi fansi-output"><CODE># Zoomed table: academy
# A tibble: 80 × 4
 film award_type status release_d…
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; 
 1 Toy Story Animated Feature Award not yet intro… 1995-11-22
 2 Toy Story Original Screenplay Nominated 1995-11-22
 3 Toy Story Adapted Screenplay Ineligible 1995-11-22
 4 Toy Story Original Score Nominated 1995-11-22
 5 Toy Story Original Song Nominated 1995-11-22
 6 Toy Story Other Won Special Achieve… 1995-11-22
 7 A Bug&#039;s Life Animated Feature Award not yet intro… 1998-11-25
 8 A Bug&#039;s Life Adapted Screenplay Ineligible 1998-11-25
 9 A Bug&#039;s Life Original Score Nominated 1998-11-25
10 Toy Story 2 Animated Feature Award not yet intro… 1999-11-24
# … with 70 more rows
</CODE></PRE>
]

---

# Flattening

Join a table to all related tables.

```r
pixar_dm %>%
  dm_flatten_to_tbl(academy)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 80 × 7
 film award_type status number release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Feature Award not yet introduced 1 1995-11-22 81 G 
 2 Toy Story Original Screenplay Nominated 1 1995-11-22 81 G 
 3 Toy Story Adapted Screenplay Ineligible 1 1995-11-22 81 G 
 4 Toy Story Original Score Nominated 1 1995-11-22 81 G 
 5 Toy Story Original Song Nominated 1 1995-11-22 81 G 
 6 Toy Story Other Won Special Achievement 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Feature Award not yet introduced 2 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Screenplay Ineligible 2 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Score Nominated 2 1998-11-25 95 G 
10 Toy Story 2 Animated Feature Award not yet introduced 3 1999-11-24 92 G 
# … with 70 more rows
</CODE></PRE>

---

# Flattening

A larger example with a different dataset.

```r
dm_nycflights13() %>%
  dm_select(weather, -year, -month, -day, -hour) %>%
  dm_flatten_to_tbl(flights)
```

```
Renaming ambiguous columns: %>%
  dm_rename(flights, flights.year = year) %>%
  dm_rename(airlines, airlines.name = name) %>%
  dm_rename(airports, airports.name = name) %>%
  dm_rename(planes, planes.year = year)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 1,761 × 44
 flights.year month day dep_time sched_de… dep_d… arr_t… sched… arr_d… carri… flight tailn… origin dest air_t… dista… hour minute
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;chr&gt; &lt;int&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
 1 2013 1 10 3 2359 4 426 437 -11 B6 727 N571JB JFK BQN 183 1576 23 59
 2 2013 1 10 16 2359 17 447 444 3 B6 739 N564JB JFK PSE 191 1617 23 59
 3 2013 1 10 450 500 -10 634 648 -14 US 1117 N171US EWR CLT 78 529 5 0
 4 2013 1 10 520 525 -5 813 820 -7 UA 1018 N35204 EWR IAH 215 1400 5 25
 5 2013 1 10 530 530 0 824 829 -5 UA 404 N815UA LGA IAH 210 1416 5 30
 6 2013 1 10 531 540 -9 832 850 -18 AA 1141 N5EAAA JFK MIA 149 1089 5 40
 7 2013 1 10 535 540 -5 1015 1017 -2 B6 725 N784JB JFK BQN 191 1576 5 40
 8 2013 1 10 546 600 -14 645 709 -24 B6 380 N337JB EWR BOS 39 200 6 0
 9 2013 1 10 549 600 -11 652 724 -32 EV 6055 N19554 LGA IAD 48 229 6 0
10 2013 1 10 550 600 -10 649 703 -14 US 2114 N740UW LGA BOS 36 184 6 0
# … with 1,751 more rows, and 26 more variables: time_hour &lt;dttm&gt;, airlines.name &lt;chr&gt;, airports.name &lt;chr&gt;, lat &lt;dbl&gt;, lon &lt;dbl&gt;,
# alt &lt;dbl&gt;, tz &lt;dbl&gt;, dst &lt;chr&gt;, tzone &lt;chr&gt;, planes.year &lt;int&gt;, type &lt;chr&gt;, manufacturer &lt;chr&gt;, model &lt;chr&gt;, engines &lt;int&gt;,
# seats &lt;int&gt;, speed &lt;int&gt;, engine &lt;chr&gt;, temp &lt;dbl&gt;, dewp &lt;dbl&gt;, humid &lt;dbl&gt;, wind_dir &lt;dbl&gt;, wind_speed &lt;dbl&gt;, wind_gust &lt;dbl&gt;,
# precip &lt;dbl&gt;, pressure &lt;dbl&gt;, visib &lt;dbl&gt;
</CODE></PRE>

---

background-image: url("data:image/png;base64,#images/23-frame.webp")
background-size: 40%
background-position: 100% 100%

# Data models: Exercises

.pull-left[
- Experiment!
]

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- **Playing the whole game**
]

## Bonus: Bring your own data + questions

---

background-image: url("data:image/png;base64,#images/24.webp")
background-size: 40%
background-position: 100% 100%

# The whole game

.pull-left[
- Build a local data model
- Copy it to the database
- Consume it

Script: `databases_24.R`

```r
library(tidyverse)
library(dm)
```
]

---

background-image: url("data:image/png;base64,#images/24-frame.webp")
background-size: 40%
background-position: 100% 100%

# The whole game

.pull-left[
- Build a local data model
- Copy it to the database
- Consume it

Script: `databases_24.R`

```r
library(tidyverse)
library(dm)
```
]

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Define transformed table

- Fix type of `number` column
- Extract `franchise` and `sequel` columns

```r
pixar_films_clean
```

<PRE class="fansi fansi-output"><CODE># A tibble: 26 × 7
 number film franchise sequel release_date run_time film_rating
 &lt;int&gt; &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story Toy Story 1 1995-11-22 81 G 
 2 2 A Bug&#039;s Life A Bug&#039;s Life NA 1998-11-25 95 G 
 3 3 Toy Story 2 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. Monsters, Inc. NA 2001-11-02 92 G 
 5 5 Finding Nemo Finding Nemo NA 2003-05-30 100 G 
 6 6 The Incredibles The Incredibles NA 2004-11-05 115 PG 
 7 7 Cars Cars 1 2006-06-09 117 G 
 8 8 Ratatouille Ratatouille NA 2007-06-29 111 G 
 9 9 WALL-E WALL-E NA 2008-06-27 98 G 
10 10 Up Up NA 2009-05-29 96 PG 
# … with 16 more rows
</CODE></PRE>

---

# Define dm object

Use `dm()` to create a dm object, pass tables (data frames or lazy tables).

```r
base_dm <- dm(
 pixar_films = pixar_films_clean,
 academy = pixarfilms::academy,
 box_office = pixarfilms::box_office,
)
base_dm
```

<PRE class="fansi fansi-output"><CODE>── Metadata ───────────────────────────────────────────────────────
Tables: `pixar_films`, `academy`, `box_office`
Columns: 15
Primary keys: 0
Foreign keys: 0
</CODE></PRE>

---

# Add keys

Using `dm_add_pk()` and `dm_add_fk()`.

.pull-left[

```r
full_dm <-
 base_dm %>%
 dm_add_pk(pixar_films, film) %>%
 dm_add_pk(box_office, film) %>%
 dm_add_fk(academy, film, pixar_films) %>%
 dm_add_fk(box_office, film, pixar_films)
full_dm
```

<PRE class="fansi fansi-output"><CODE>── Metadata ───────────────────────────────────────────────────────
Tables: `pixar_films`, `academy`, `box_office`
Columns: 15
Primary keys: 2
Foreign keys: 2
</CODE></PRE>

```r
full_dm %>%
  dm_draw(view_type = "all")
```
]

.pull-right[


<svg width="252pt" height="230pt"
 viewBox="0.00 0.00 252.00 230.00" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
<g id="graph0" class="graph" transform="scale(1 1) rotate(0) translate(4 226)">
<title>%0</title>
<g id="a_graph0"><a xlink:title="Data Model">
<polygon fill="#ffffff" stroke="transparent" points="-4,4 -4,-226 248,-226 248,4 -4,4"/>
</a>
</g>

<g id="node1" class="node">
<title>academy</title>
<polygon fill="#efebdd" stroke="transparent" points="32.5,-201 32.5,-221 101.5,-221 101.5,-201 32.5,-201"/>
<text text-anchor="start" x="42.1255" y="-206.4" font-family="Times,serif" font-size="14.00" fill="#000000">academy</text>
<polygon fill="#ffffff" stroke="transparent" points="32.5,-181 32.5,-201 101.5,-201 101.5,-181 32.5,-181"/>
<text text-anchor="start" x="34.5" y="-186.4" font-family="Times,serif" font-size="14.00" fill="#444444">film</text>
<polygon fill="#ffffff" stroke="transparent" points="32.5,-161 32.5,-181 101.5,-181 101.5,-161 32.5,-161"/>
<text text-anchor="start" x="34.3492" y="-166.4" font-family="Times,serif" font-size="14.00" fill="#444444">award_type</text>
<polygon fill="#ffffff" stroke="transparent" points="32.5,-141 32.5,-161 101.5,-161 101.5,-141 32.5,-141"/>
<text text-anchor="start" x="34.5" y="-146.4" font-family="Times,serif" font-size="14.00" fill="#444444">status</text>
<polygon fill="none" stroke="#555555" points="31,-140 31,-222 102,-222 102,-140 31,-140"/>
</g>

<g id="node3" class="node">
<title>pixar_films</title>
<polygon fill="#efebdd" stroke="transparent" points="170.5,-171 170.5,-191 243.5,-191 243.5,-171 170.5,-171"/>
<text text-anchor="start" x="174.7286" y="-176.4" font-family="Times,serif" font-size="14.00" fill="#000000">pixar_films</text>
<polygon fill="#ffffff" stroke="transparent" points="170.5,-151 170.5,-171 243.5,-171 243.5,-151 170.5,-151"/>
<text text-anchor="start" x="172.5" y="-156.4" font-family="Times,serif" font-size="14.00" fill="#444444">number</text>
<polygon fill="#ffffff" stroke="transparent" points="170.5,-131 170.5,-151 243.5,-151 243.5,-131 170.5,-131"/>
<text text-anchor="start" x="172.5" y="-137.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="#ffffff" stroke="transparent" points="170.5,-111 170.5,-131 243.5,-131 243.5,-111 170.5,-111"/>
<text text-anchor="start" x="172.5" y="-116.4" font-family="Times,serif" font-size="14.00" fill="#444444">franchise</text>
<polygon fill="#ffffff" stroke="transparent" points="170.5,-91 170.5,-111 243.5,-111 243.5,-91 170.5,-91"/>
<text text-anchor="start" x="172.5" y="-96.4" font-family="Times,serif" font-size="14.00" fill="#444444">sequel</text>
<polygon fill="#ffffff" stroke="transparent" points="170.5,-71 170.5,-91 243.5,-91 243.5,-71 170.5,-71"/>
<text text-anchor="start" x="172.413" y="-76.4" font-family="Times,serif" font-size="14.00" fill="#444444">release_date</text>
<polygon fill="#ffffff" stroke="transparent" points="170.5,-51 170.5,-71 243.5,-71 243.5,-51 170.5,-51"/>
<text text-anchor="start" x="172.5" y="-56.4" font-family="Times,serif" font-size="14.00" fill="#444444">run_time</text>
<polygon fill="#ffffff" stroke="transparent" points="170.5,-31 170.5,-51 243.5,-51 243.5,-31 170.5,-31"/>
<text text-anchor="start" x="172.5" y="-36.4" font-family="Times,serif" font-size="14.00" fill="#444444">film_rating</text>
<polygon fill="none" stroke="#555555" points="169,-30 169,-192 244,-192 244,-30 169,-30"/>
</g>

<g id="edge1" class="edge">
<title>academy:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M101.5,-191C135.5254,-191 133.5378,-150.6405 160.2481,-142.4425"/>
<polygon fill="#555555" stroke="#555555" points="161.0852,-145.8593 170.5,-141 160.1099,-138.9275 161.0852,-145.8593"/>
</g>

<g id="node2" class="node">
<title>box_office</title>
<polygon fill="#efebdd" stroke="transparent" points="1.5,-101 1.5,-121 132.5,-121 132.5,-101 1.5,-101"/>
<text text-anchor="start" x="36.6795" y="-106.4" font-family="Times,serif" font-size="14.00" fill="#000000">box_office</text>
<polygon fill="#ffffff" stroke="transparent" points="1.5,-81 1.5,-101 132.5,-101 132.5,-81 1.5,-81"/>
<text text-anchor="start" x="3.5" y="-87.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="#ffffff" stroke="transparent" points="1.5,-61 1.5,-81 132.5,-81 132.5,-61 1.5,-61"/>
<text text-anchor="start" x="3.5" y="-66.4" font-family="Times,serif" font-size="14.00" fill="#444444">budget</text>
<polygon fill="#ffffff" stroke="transparent" points="1.5,-41 1.5,-61 132.5,-61 132.5,-41 1.5,-41"/>
<text text-anchor="start" x="3.5" y="-46.4" font-family="Times,serif" font-size="14.00" fill="#444444">box_office_us_canada</text>
<polygon fill="#ffffff" stroke="transparent" points="1.5,-21 1.5,-41 132.5,-41 132.5,-21 1.5,-21"/>
<text text-anchor="start" x="3.5" y="-26.4" font-family="Times,serif" font-size="14.00" fill="#444444">box_office_other</text>
<polygon fill="#ffffff" stroke="transparent" points="1.5,-1 1.5,-21 132.5,-21 132.5,-1 1.5,-1"/>
<text text-anchor="start" x="3.2447" y="-6.4" font-family="Times,serif" font-size="14.00" fill="#444444">box_office_worldwide</text>
<polygon fill="none" stroke="#555555" points="0,0 0,-122 133,-122 133,0 0,0"/>
</g>

<g id="edge2" class="edge">
<title>box_office:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M132.5,-91C156.2685,-91 146.8719,-127.2579 160.7946,-138.022"/>
<polygon fill="#555555" stroke="#555555" points="159.9132,-141.4126 170.5,-141 161.9666,-134.7205 159.9132,-141.4126"/>
</g>
</g>
</svg>
]

---

# ETL, revisited

All tables and keys in a dm object can be copied in one swoop.

```r
db_path <- fs::path_abs("pixar.duckdb")
fs::file_delete(db_path)
con <- DBI::dbConnect(duckdb::duckdb(dbdir = db_path))
pixar_dm_duckdb <- copy_dm_to(con, full_dm, temporary = FALSE)
```

```
Warning: duckdb doesn't support foreign keys, these won't be set in
the remote database but are preserved in the `dm`
```

```r
pixar_dm_duckdb
```

<PRE class="fansi fansi-output"><CODE>── Table source ───────────────────────────────────────────────────
src: duckdb_connection
── Metadata ───────────────────────────────────────────────────────
Tables: `pixar_films`, `academy`, `box_office`
Columns: 15
Primary keys: 2
Foreign keys: 2
</CODE></PRE>

---

# ETL, revisited

The `temporary = FALSE` argument ensures that tables are permanent.

```r
pixar_dm_duckdb$pixar_films
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;&quot;pixar_films&quot;&gt; [?? x 7]
# Database: duckdb_connection
 number film franchise sequel release_date run_time film_rating
 &lt;int&gt; &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 1 Toy Story Toy Story 1 1995-11-22 81 G 
 2 2 A Bug&#039;s Life A Bug&#039;s Life NA 1998-11-25 95 G 
 3 3 Toy Story 2 Toy Story 2 1999-11-24 92 G 
 4 4 Monsters, Inc. Monsters, Inc. NA 2001-11-02 92 G 
 5 5 Finding Nemo Finding Nemo NA 2003-05-30 100 G 
 6 6 The Incredibles The Incredibles NA 2004-11-05 115 PG 
 7 7 Cars Cars 1 2006-06-09 117 G 
 8 8 Ratatouille Ratatouille NA 2007-06-29 111 G 
 9 9 WALL-E WALL-E NA 2008-06-27 98 G 
10 10 Up Up NA 2009-05-29 96 PG 
# … with more rows
</CODE></PRE>

---

# ETL, revisited

Operations on the database dm work the same as for the local dm.

```r
pixar_dm_duckdb %>%
  dm_flatten_to_tbl(academy)
```

<PRE class="fansi fansi-output"><CODE># Source: lazy query [?? x 9]
# Database: duckdb_connection
 film award_type status number franchise sequel release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;chr&gt; &lt;int&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
 1 Toy Story Animated Feature Award not yet introduced 1 Toy Story 1 1995-11-22 81 G 
 2 Toy Story Original Screenplay Nominated 1 Toy Story 1 1995-11-22 81 G 
 3 Toy Story Adapted Screenplay Ineligible 1 Toy Story 1 1995-11-22 81 G 
 4 Toy Story Original Score Nominated 1 Toy Story 1 1995-11-22 81 G 
 5 Toy Story Original Song Nominated 1 Toy Story 1 1995-11-22 81 G 
 6 Toy Story Other Won Special Achievement 1 Toy Story 1 1995-11-22 81 G 
 7 A Bug&#039;s Life Animated Feature Award not yet introduced 2 A Bug&#039;s Life NA 1998-11-25 95 G 
 8 A Bug&#039;s Life Adapted Screenplay Ineligible 2 A Bug&#039;s Life NA 1998-11-25 95 G 
 9 A Bug&#039;s Life Original Score Nominated 2 A Bug&#039;s Life NA 1998-11-25 95 G 
10 Toy Story 2 Animated Feature Award not yet introduced 3 Toy Story 2 1999-11-24 92 G 
# … with more rows
</CODE></PRE>

---

# Consume, revisited

Keys are not (yet) loaded for DuckDB.
This works better for SQL Server and Postgres.

```r
DBI::dbDisconnect(con)

con <- DBI::dbConnect(duckdb::duckdb(dbdir = db_path))
pixar_dm_learned <- dm_from_src(con)
```

```
Keys could not be queried, use `learn_keys = FALSE` to mute this message.
```

```r
pixar_dm_learned
```

<PRE class="fansi fansi-output"><CODE>── Table source ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
src: duckdb_connection
── Metadata ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Tables: `academy`, `box_office`, `pixar_films`
Columns: 15
Primary keys: 0
Foreign keys: 0
</CODE></PRE>

---

# Consume, revisited

Implement a helper function for your data model.

.pull-left[

```r
dm_pixarfilms_small <- function() {
 db_path <- fs::path_abs("pixar.duckdb")
 con <- DBI::dbConnect(duckdb::duckdb(dbdir = db_path))
 table_names <- c("academy", "box_office", "pixar_films")
 dm_from_src(
 con, 
 table_names = table_names, 
 learn_keys = FALSE
 ) %>%
 dm_add_pk(pixar_films, film) %>%
 dm_add_pk(box_office, film) %>%
 dm_add_fk(academy, film, pixar_films) %>%
 dm_add_fk(box_office, film, pixar_films)
}
```
]

.pull-right[

```r
dm_pixarfilms_small()
```

<PRE class="fansi fansi-output"><CODE>── Table source ───────────────────────────────────────────────────
src: duckdb_connection
── Metadata ───────────────────────────────────────────────────────
Tables: `academy`, `box_office`, `pixar_films`
Columns: 15
Primary keys: 2
Foreign keys: 2
</CODE></PRE>
]

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Consuming with dm

Create a new derived table in the dm object.

.pull-left[

```r
my_dm <- dm_pixarfilms_small()
my_helper_dm <-
 my_dm %>%
 dm_zoom_to(academy) %>%
 filter(status == "Won") %>%
 count(film, name = "n_awards_won") %>%
 dm_insert_zoomed("academy_won")
```
]

.pull-right[

```r
my_helper_dm
```

<PRE class="fansi fansi-output"><CODE>── Table source ───────────────────────────────────────────────────
src: duckdb_connection
── Metadata ───────────────────────────────────────────────────────
Tables: `academy`, `box_office`, `pixar_films`, `academy_won`
Columns: 17
Primary keys: 2
Foreign keys: 3
</CODE></PRE>
]

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Consuming with dm

Use the derived table to cross-reference box office and awards won per film.

.pull-left[

```r
box_office_vs_awards <-
 my_helper_dm %>%
 dm_zoom_to(pixar_films) %>%
 left_join(
 box_office, 
 select = c(film, box_office_worldwide)
 ) %>%
 left_join(academy_won) %>%
 transmute(
 rating = film_rating,
 box_office_mln = box_office_worldwide / 1e6,
 n_awards_won = coalesce(n_awards_won, 0)
 ) %>%
 pull_tbl() %>%
 collect()
```
]

.pull-right[

```r
box_office_vs_awards
```

<PRE class="fansi fansi-output"><CODE># A tibble: 26 × 3
 rating box_office_mln n_awards_won
 &lt;chr&gt; &lt;dbl&gt; &lt;dbl&gt;
 1 G 632. 1
 2 G 871. 1
 3 PG 632. 2
 4 G 624. 1
 5 G 521. 1
 6 PG 735. 2
 7 G 1067. 2
 8 PG 539. 1
 9 PG 858. 1
10 PG 807. 2
# … with 16 more rows
</CODE></PRE>
]

---

# Consuming with dm

```r
ggplot(box_office_vs_awards, aes(x = box_office_mln, y = n_awards_won)) +
  geom_smooth() +
  geom_point() +
  facet_wrap(vars(rating)) +
  theme_bw(20)
```

![](data:image/png;base64,#databases_files/figure-html/databases-170-1.png)

---

background-image: url("data:image/png;base64,#images/24-frame.webp")
background-size: 40%
background-position: 100% 100%

# The whole game: Exercises

.pull-left[
- Experiment
]

---

# Recap

---

# Schedule

Extract ⇨ Transform ⇨ Load ⇨ Consume

.pull-left[
## One table

- Read whole tables
- Let the database do the heavy lifting
- Basic ETL for one table
- Subtle issues to watch out for
]

.pull-right[
## Multiple tables

- Joins
- The {dm} package
- A bit of theory
- Playing the whole game
]

## Bonus: Bring your own data + questions